import pandas as pd
import requests
import urllib.parse
import time
from concurrent.futures import ThreadPoolExecutor, as_completed
from tqdm import tqdm

# 高德API的Key
API_KEY = 'ffe78c872f5a575afb4ab357d3bb77bf'  # 请替换为你自己的高德API密钥

# 输入和输出的Excel文件路径
input_file_path = 'detail.xlsx'    # 请替换为你的输入Excel文件路径

# 读取Excel文件
try:
    df = pd.read_excel(input_file_path)
except Exception as e:
    print(f"读取Excel文件时出错: {e}")
    exit(1)

# 检查地址列是否存在
if '地址' not in df.columns:
    print("Excel文件中未找到名为 '地址' 的列。请确保存在该列。")
    exit(1)

# 定义高德API请求的URL模板，添加city参数限制在济南市
geocode_url_template = "https://restapi.amap.com/v3/geocode/geo?key={}&address={}&city={}"

# 创建新的列用于保存区、经度和纬度
df['区'] = ''
df['经度'] = ''
df['纬度'] = ''

# 定义API调用函数
def fetch_geocode(index, address):
    # 对地址进行URL编码
    encoded_address = urllib.parse.quote(address)
    
    # 设置城市为济南，确保查询限制在济南市
    city = '济南'
    
    request_url = geocode_url_template.format(API_KEY, encoded_address, urllib.parse.quote(city))
    
    try:
        response = requests.get(request_url, timeout=10)  # 设置超时时间为10秒
        response.raise_for_status()  # 如果响应状态码不是200，将引发HTTPError
        data = response.json()
        
        if data.get('status') == '1' and data.get('geocodes'):
            geocode_info = data['geocodes'][0]
            district = geocode_info.get('district', '')  # 获取区信息
            location = geocode_info.get('location', '')  # 获取经纬度
            if location:
                try:
                    lng, lat = location.split(',')
                    return index, district, float(lng), float(lat)
                except ValueError:
                    print(f"解析经纬度时出错，地址: {address}, location: {location}")
                    return index, '', '', ''
        else:
            print(f"未能获取地址 {address} 的地理信息。响应: {data.get('info', '无信息')}")
            return index, '', '', ''
    except requests.RequestException as e:
        print(f"请求地址 {address} 时出错: {e}")
        return index, '', '', ''
    except ValueError:
        print(f"解析JSON响应时出错，地址: {address}")
        return index, '', '', ''

# 使用 ThreadPoolExecutor 来创建线程池
with ThreadPoolExecutor(max_workers=4) as executor:
    futures = {executor.submit(fetch_geocode, index, row['地址']): index for index, row in df.iterrows() if not pd.isna(row['地址'])}
    
    for future in tqdm(as_completed(futures), total=len(futures), desc="处理地址"):
        index, district, lng, lat = future.result()
        if district:
            df.at[index, '区'] = district
        if lng:
            df.at[index, '经度'] = lng
        if lat:
            df.at[index, '纬度'] = lat

# 保存更新后的Excel文件
try:
    df.to_excel(input_file_path, index=False)
    print(f"已将结果保存到 {input_file_path}")
except Exception as e:
    print(f"保存Excel文件时出错: {e}")
